package com.wjw.utils.office;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @desc Excel解析工具类
 * @author wjw
 * @date 2016年11月23日下午6:52:32
 */
public final class ExcelUtil {

    /**
     * 日志对象
     */
    protected static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
	
	/**
	 * 解析Excel,读取第一个工作簿
	 * @param path Excel路径
	 * @return
	 */
	public static Sheet readExcel(String path){
		File file = null;
		InputStream input = null;
		if(path!=null&&path.length()>0){
			//判断文件是否是Excel(2003、2007)
			String suffix = path.substring(path.lastIndexOf("."),path.length());
			file = new File(path);
			try {
				input = new FileInputStream(file);
			} catch (FileNotFoundException e) {
				log.error("未找到指定的文件！");
			}
			if (".xls".equalsIgnoreCase(suffix)) {// 2003
				//System.out.println("Excel为2003版本");
				POIFSFileSystem fileSystem = null;
				HSSFWorkbook workBook = null;//工作簿
				try {
					fileSystem = new POIFSFileSystem(input);
					workBook = new HSSFWorkbook(fileSystem);
				} catch (IOException e) {
					e.printStackTrace();
				}
				HSSFSheet sheet = workBook.getSheetAt(0);//获取第一个工作簿
				return sheet;
			} else if (".xlsx".equalsIgnoreCase(suffix)) {// 2007
				//System.out.println("Excel为2007版本");
				XSSFWorkbook workBook = null;
				try {
					workBook = new XSSFWorkbook(input);
				} catch (IOException e) {
					e.printStackTrace();
				}
				XSSFSheet sheet = workBook.getSheetAt(0);//获取第一个工作簿\
				return sheet;
			}
		}else{
			log.error(" 非法的文件路径!");
			throw new IllegalArgumentException(" 非法的文件路径!");
		}
		return null;
	}
	
	/**
	 * 通过工作簿获取Excel内容
	 * @param sheet
	 * @return
	 */
	public static void getContent(Sheet sheet){
		Row row=null;  //execel行
		Cell cell = null;//execel单元格			
		for(int i=0,rowCount = sheet.getLastRowNum();i<=rowCount;i++){//遍历行
			row = sheet.getRow(i);
			if(row==null){//如果行为空则返回
				continue;
			}
			for(int j=0,cellCount=row.getLastCellNum();j<cellCount;j++){//遍历行的单元格
				cell = row.getCell(j);
				if(cell==null){//空单元格跳出
					continue;
				}
				//判断是否具有合并单元格  
				boolean isMerge = ExcelUtil.isMergedRegion(sheet, i, cell.getColumnIndex());  
				//课表班级 星期
				if(isMerge) {
					String rs = ExcelUtil.getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex()).trim();
//					rs为单元格
					System.out.println(rs);
				}else {  
					String rs=(cell.getRichStringCellValue()+"  ").trim();
//					rs为单元格
					System.out.println(rs);
				}
			}
		}
	}
	
	/**
	 * 将excel(2003和2007)转换成html
	 * @param excelPath
	 * @param htmlPath
	 */
	public static void excel2Html(String excelPath,String htmlPath){
		if(excelPath!=null&&excelPath.length()>7){
			if(!new File(excelPath).exists()){
				log.error("ecxel文件不存在！");
				return;
			}
			//判断文件是否是Excel(2003、2007)
			String suffix = excelPath.substring(excelPath.lastIndexOf("."),excelPath.length());
			if (".xls".equalsIgnoreCase(suffix)) {// 2003
				excel2003ToHtml(excelPath, htmlPath);
			}else if(".xlsx".equalsIgnoreCase(suffix)){
				excel2007ToHtml(excelPath, htmlPath);
			}else{
				log.error("不是excel文件");
			}
		}else{
			log.error("非法的文件路径!");
		}
	}
	/**
	 * Excel(2003) Sheet转换为html
	 * @param sheet
	 * @param workbook 
	 * @return
	 * @throws UnsupportedEncodingException 
	 */
	public static void excel2003ToHtml(String excelPath,String htmlPath){
		FileInputStream inp=null;
		try {
			inp = new FileInputStream(excelPath);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
		HSSFWorkbook workbook=null;
		try {
			workbook = new HSSFWorkbook(inp);
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		HSSFSheet sheet = workbook.getSheetAt(0);
		File _html = new File(htmlPath);
		FileOutputStream stream;// provides file access 
		OutputStreamWriter writer;// writes to the file 
		int row = sheet.getLastRowNum();
		HSSFRow _row = sheet.getRow(0);
		int col = _row.getLastCellNum();
		Object[][][] tdinfo = new Object[row][col][2];
		Map<Object,Object> style = new HashMap<Object,Object>();
		// 合并单元格
		for(int i=0;i<sheet.getNumMergedRegions();i++){
			CellRangeAddress cr=sheet.getMergedRegion(i);
			int rs=cr.getFirstRow();
			int re=cr.getLastRow();
			int cs=cr.getFirstColumn();
			int ce=cr.getLastColumn();
			tdinfo[rs][cs][0] = "";
			if(re > rs){
				tdinfo[rs][cs][0] = tdinfo[rs][cs][0]+ " rowspan='"+(re-rs+1)+"'";
			}
			if(ce>cs){
				tdinfo[rs][cs][0] = tdinfo[rs][cs][0]+ " colspan='"+(ce-cs+1)+"'";
			}
			for(int x=rs;x<=re;x++){
				for(int y=cs;y<=ce;y++){
					if(x!=rs || y!=cs){
						tdinfo[x][y] = null;
					}
				}
			}
		}
		float[] width = new float[col];
		int widthsum = 0;
		int max = 0;
		// 列宽
		for(int i=0;i<col;i++){
			width[i] = sheet.getColumnWidth(i);
			if(width[i]>=width[max]){
				max = i;
			}
			widthsum += width[i];
		}
		// 最宽的一列不指定宽度
		width[max] = 0;
		// 设置单元格内容
		for(int i=0;i<row;i++){
			_row = sheet.getRow(i);
			for(int j=0;j<col;j++){
				if(tdinfo[i][j] == null){
					continue;
				}
				HSSFCell cell = _row.getCell(j);
				if(cell != null){
					HSSFCellStyle s = cell.getCellStyle();
					if(tdinfo[i][j][0] == null){
						tdinfo[i][j][0] = "";
					}
					// 设置单元格的样式
					tdinfo[i][j][0] = tdinfo[i][j][0] + " class='"+get2003CssByStyle(s,workbook,style)+"'";
					// 设置单元格的值
					tdinfo[i][j][1]=getCellValue(cell);
				}else{
					tdinfo[i][j] = null;
				}
			}
		}
				
		StringBuffer br = new StringBuffer();
		br.append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
		br.append("<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>");
		br.append("<style>");
		Iterator<Object> it = style.values().iterator();
		while(it.hasNext()){
			String[] css = (String[])it.next();
			br.append(css[1]);
		}
		br.append("</style></head><body>");
		br.append("<table cellspacing='0' cellpadding='0' style='border-collapse:collapse;'>");
		// 设置单元格的宽度
		for(int i=0;i<col;i++){
			if(i != max){
				br.append("<col width='"+Math.rint(width[i]/widthsum*100)+"%'>");
			}else{
				br.append("<col>");
			}
		}
		for(int i=0;i<row;i++){
			br.append("<tr>");
			for(int j=0;j<col;j++){
				if(tdinfo[i][j] != null){
					if(tdinfo[i][j][0] == null){
						tdinfo[i][j][0] = "";
					}
					if(tdinfo[i][j][1]==null){
						tdinfo[i][j][1] = " ";
					}
					br.append("<td "+tdinfo[i][j][0]+">"+tdinfo[i][j][1]+"</td>");
				}
			}
			br.append("</tr>");
		}
		br.append("</table></body></html>");
		try {
			stream = new FileOutputStream(_html); 
			writer = new OutputStreamWriter(stream,"UTF-8");
			writer.write(br.toString());
			writer.flush();
			writer.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	/**获取2003下的颜色
	 * 取RGB颜色
	 * @param c
	 * @return
	 */
	public static String get2003Color(short c){
		if(c<8||c>63){
			return "";
		}
		String color = ((HSSFColor)HSSFColor.getIndexHash().get(new Integer(c))).getHexString();
		String[] cs = color.split(":");
		color = "#";
		for(int j=0;j<cs.length;j++){
			if(cs[j].length()==1){
				color+=cs[j]+cs[j];
			}else if(cs[j].length()==4){
				color+=cs[j].substring(2);
			}else{
				color+=cs[j];
			}
		}
		return color;
	}
	
	/**
	 * 获取2003下的样式
	 * @param s
	 * @param workbook
	 * @param style
	 * @return
	 */
	public static String get2003CssByStyle(HSSFCellStyle s, HSSFWorkbook workbook,Map<Object,Object> style) {
		if(style.containsKey(s)){
			String[] css = (String[])style.get(s);
			return css[0];
		}else{
			String[] css = new String[2];
			css[0] = "c"+style.size();
			StringBuffer cssinfo = new StringBuffer();
			// 文字对齐方式
			switch(s.getAlignment()){
				case HSSFCellStyle.ALIGN_CENTER:
					cssinfo.append("text-align:center;");break;
				case HSSFCellStyle.ALIGN_LEFT:
					cssinfo.append("text-align:left;");break;
				case HSSFCellStyle.ALIGN_RIGHT:
					cssinfo.append("text-align:right;");break;
			}
			// 背景色
			cssinfo.append("background-color:"+get2003Color(s.getFillForegroundColor())+";");
			// 设置边框
			cssinfo.append("border-top:"+s.getBorderTop()+"px solid #000000;");
			cssinfo.append("border-left:"+s.getBorderLeft()+"px solid #000000;");
			cssinfo.append("border-right:"+s.getBorderRight()+"px solid #000000;");
			cssinfo.append("border-bottom:"+s.getBorderBottom()+"px solid #000000;");
			// 设置字体
			HSSFFont font = workbook.getFontAt(s.getFontIndex());
			cssinfo.append("font-size:"+font.getFontHeightInPoints()+"pt;");
			if(HSSFFont.BOLDWEIGHT_BOLD == font.getBoldweight()){
				cssinfo.append("font-weight: bold;");
			}
			cssinfo.append("font-family: "+font.getFontName()+";");
			if(font.getItalic()){
				cssinfo.append("font-style: italic;");
			}
			String fontcolor = get2003Color(font.getColor());{
				if(fontcolor.trim().length() > 0){
					cssinfo.append("color: "+fontcolor+";");
				}
			}
			css[1] = "."+css[0]+"{"+cssinfo.toString()+"}";
			style.put(s, css);
			return css[0];
		}
	}
	
	
	/**
	 * @param sheet
	 * @param workbook 
	 * @return
	 * @throws UnsupportedEncodingException 
	 */
	public static void excel2007ToHtml(String excelPath,String htmlPath){
		FileInputStream inp=null;
		try {
			inp = new FileInputStream(excelPath);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
		XSSFWorkbook workbook=null;
		try {
			workbook = new XSSFWorkbook(inp);
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		XSSFSheet sheet = workbook.getSheetAt(0);
		File _html = new File(htmlPath);
		FileOutputStream stream;// provides file access 
		OutputStreamWriter writer;// writes to the file 
		int row = sheet.getLastRowNum();
		XSSFRow _row = sheet.getRow(0);
		int col = _row.getLastCellNum();
		Object[][][] tdinfo = new Object[row][col][2];
		Map<Object,Object> style = new HashMap<Object,Object>();
		// 合并单元格
		for(int i=0;i<sheet.getNumMergedRegions();i++){
			CellRangeAddress cr=sheet.getMergedRegion(i);
			int rs=cr.getFirstRow();
			int re=cr.getLastRow();
			int cs=cr.getFirstColumn();
			int ce=cr.getLastColumn();
			tdinfo[rs][cs][0] = "";
			if(re > rs){
				tdinfo[rs][cs][0] = tdinfo[rs][cs][0] + " rowspan='"+(re-rs+1)+"'";
			}
			if(ce>cs){
				tdinfo[rs][cs][0] = tdinfo[rs][cs][0] + " colspan='"+(ce-cs+1)+"'";
			}
			for(int x=rs;x<=re;x++){
				for(int y=cs;y<=ce;y++){
					if(x!=rs || y!=cs){
						tdinfo[x][y] = null;
					}
				}
			}
		}
		float[] width = new float[col];
		int widthsum = 0;
		int max = 0;
		// 列宽
		for(int i=0;i<col;i++){
			width[i] = sheet.getColumnWidth(i);
			if(width[i]>=width[max]){
				max = i;
			}
			widthsum += width[i];
		}
		// 最宽的一列不指定宽度
		width[max] = 0;
		// 设置单元格内容
		for(int i=0;i<row;i++){
			_row = sheet.getRow(i);
			for(int j=0;j<col;j++){
				if(tdinfo[i][j] == null){
					continue;
				}
				XSSFCell cell = _row.getCell(j);
				if(cell != null){
					XSSFCellStyle s = cell.getCellStyle();
					if(tdinfo[i][j][0] == null){
						tdinfo[i][j][0] = "";
					}
					// 设置单元格的样式
					tdinfo[i][j][0] = tdinfo[i][j][0] + " class='"+get2007CssByStyle(s,workbook,style)+"'";
					// 设置单元格的值
					tdinfo[i][j][1]=getCellValue(cell);
				}else{
					tdinfo[i][j] = null;
				}
			}
		}
				
		StringBuffer br = new StringBuffer();
		br.append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
		br.append("<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>");
		br.append("<style>");
		Iterator<Object> it = style.values().iterator();
		while(it.hasNext()){
			String[] css = (String[])it.next();
			br.append(css[1]);
		}
		br.append("</style></head><body>");
		br.append("<table cellspacing='0' cellpadding='0' style='border-collapse:collapse;'>");
		// 设置单元格的宽度
		for(int i=0;i<col;i++){
			if(i != max){
				br.append("<col width='"+Math.rint(width[i]/widthsum*100)+"%'>");
			}else{
				br.append("<col>");
			}
		}
		for(int i=0;i<row;i++){
			br.append("<tr>");
			for(int j=0;j<col;j++){
				if(tdinfo[i][j] != null){
					if(tdinfo[i][j][0] == null){
						tdinfo[i][j][0] = "";
					}
					if(tdinfo[i][j][1]==null){
						tdinfo[i][j][1] = " ";
					}
					br.append("<td "+tdinfo[i][j][0]+">"+tdinfo[i][j][1]+"</td>");
				}
			}
			br.append("</tr>");
		}
		br.append("</table></body></html>");
		try {
			stream = new FileOutputStream(_html); 
			writer = new OutputStreamWriter(stream,"UTF-8");
			writer.write(br.toString());
			writer.flush();
			writer.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	/**获取2007下的颜色
	 * 取RGB颜色
	 * @param c
	 * @return
	 */
	public static String get2007Color(short c){
		if(c<8||c>63){
			return "";
		}
		String color = ((HSSFColor)HSSFColor.getIndexHash().get(new Integer(c))).getHexString();
		String[] cs = color.split(":");
		color = "#";
		for(int j=0;j<cs.length;j++){
			if(cs[j].length()==1){
				color+=cs[j]+cs[j];
			}else if(cs[j].length()==4){
				color+=cs[j].substring(2);
			}else{
				color+=cs[j];
			}
		}
		return color;
	}
	
	/**
	 * 获取2007下的样式
	 * @param s
	 * @param workbook
	 * @param style
	 * @return
	 */
	public static String get2007CssByStyle(XSSFCellStyle s, XSSFWorkbook workbook,Map<Object,Object> style) {
		if(style.containsKey(s)){
			String[] css = (String[])style.get(s);
			return css[0];
		}else{
			String[] css = new String[2];
			css[0] = "c"+style.size();
			StringBuffer cssinfo = new StringBuffer();
			// 文字对齐方式
			switch(s.getAlignment()){
				case XSSFCellStyle.ALIGN_CENTER:
					cssinfo.append("text-align:center;");break;
				case XSSFCellStyle.ALIGN_LEFT:
					cssinfo.append("text-align:left;");break;
				case XSSFCellStyle.ALIGN_RIGHT:
					cssinfo.append("text-align:right;");break;
			}
			// 背景色
			cssinfo.append("background-color:"+get2007Color(s.getFillForegroundColor())+";");
			// 设置边框
			cssinfo.append("border-top:"+s.getBorderTop()+"px solid #000000;");
			cssinfo.append("border-left:"+s.getBorderLeft()+"px solid #000000;");
			cssinfo.append("border-right:"+s.getBorderRight()+"px solid #000000;");
			cssinfo.append("border-bottom:"+s.getBorderBottom()+"px solid #000000;");
			// 设置字体
			XSSFFont font = workbook.getFontAt(s.getFontIndex());
			cssinfo.append("font-size:"+font.getFontHeightInPoints()+"pt;");
			if(HSSFFont.BOLDWEIGHT_BOLD == font.getBoldweight()){
				cssinfo.append("font-weight: bold;");
			}
			cssinfo.append("font-family: "+font.getFontName()+";");
			if(font.getItalic()){
				cssinfo.append("font-style: italic;");
			}
			String fontcolor = get2007Color(font.getColor());{
				if(fontcolor.trim().length() > 0){
					cssinfo.append("color: "+fontcolor+";");
				}
			}
			css[1] = "."+css[0]+"{"+cssinfo.toString()+"}";
			style.put(s, css);
			return css[0];
		}
	}
	
	/**
	 * 获取合并单元格的值
	 */
	public static String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if (row >= firstRow && row <= lastRow) {

				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellValue(fCell);
				}
			}
		}

		return null;
	}

	/**
	 * 判断合并了行
	 */
	public static boolean isMergedRow(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row == firstRow && row == lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 */
	public static boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 判断sheet页中是否含有合并单元格
	 */
	public static boolean hasMerged(Sheet sheet) {
		return sheet.getNumMergedRegions() > 0 ? true : false;
	}

	/**
	 * 合并单元格
	 */
	public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	/**   
	* 获取单元格的值   
	*/    
	public static String getCellValue(Cell cell){    
	        
	    if(cell == null) return "";    
	        
	    if(cell.getCellType() == Cell.CELL_TYPE_STRING){    
	            
	        return cell.getStringCellValue();    
	            
	    }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){    
	            
	        return String.valueOf(cell.getBooleanCellValue());    
	            
	    }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){    
	            
	        return cell.getCellFormula() ;    
	            
	    }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){    
	            
	        return String.valueOf(cell.getNumericCellValue());    
	            
	    }
	    return "";    
	}
}